﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Wicresoft.SMS.Data
{
    public class sqlWebAddressDataAccess : IAddressWebDataAccess
    {
        private IConnectStringProvider _connParamProvider;
        private string _connectionString;

        public sqlWebAddressDataAccess(string ConnectionString)
        {
            _connectionString = ConnectionString;
        }

        public List<AddressSendInfoModel> GetNumberByUser(int UserId, int index, int max, string key)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var s = (from n in dbModel.Number
                         join c in dbModel.Contact
                         on n.AddressID equals c.ID
                         where c.UserID == UserId
                         where c.DisplayName.Contains(key) || n.Number1.Contains(key)
                         orderby c.ID
                         select new
                         {
                             number = n.Number1,
                             name = c.DisplayName,
                             addressId = n.AddressID,
                             groupinfo = c.Group
                         }).Skip((index - 1) * max).Take(max);
                List<AddressSendInfoModel> list = new List<AddressSendInfoModel>();
                foreach (var n in s)
                {
                    AddressSendInfoModel _list = new AddressSendInfoModel();
                    _list.Name = n.name;
                    _list.Number = n.number;
                    _list.AddressId = n.addressId;
                    _list.GroupInfo = "";
                    foreach (var item in n.groupinfo)
                    {
                        _list.GroupInfo += item.GroupName + ", ";
                    }
                    if (_list.GroupInfo.Length != 0)
                        _list.GroupInfo = _list.GroupInfo.Remove(_list.GroupInfo.Length - 2);

                    if (_list.GroupInfo.Length <= 10)
                        _list.GroupInfoShort = _list.GroupInfo;
                    else
                        _list.GroupInfoShort = _list.GroupInfo.Substring(0, 10) + "...";

                    list.Add(_list);
                }
                return list;
            }
            throw new NotImplementedException();
        }

        public int AddAddress(string name, string number, int userId)
        {
            using (var dbModel = new Entities(_connectionString))
            {

                var c = dbModel.Contact.FirstOrDefault(u => u.UserID == userId && u.DisplayName == name);

                //如果已经存在此用户
                if (c != null)
                {
                    if (c.Number.Count != 0)
                        return 1;
                    else //存在且删除
                    {
                        c.Enabled = true;
                        c.Number.Clear();
                        Number num = new Number();
                        num.Number1 = number;
                        c.Number.Add(num);
                        try
                        {
                            dbModel.SaveChanges();
                        }
                        catch (Exception)
                        {
                            //return "系统出现未知错误，请稍候再试";
                            return 2;
                        }
                        return 3;
                    }
                }
                else
                {
                    Contact con = new Contact();
                    con.UserID = userId;
                    con.DisplayName = name;
                    con.Enabled = true;
                    dbModel.AddToContact(con);
                    try
                    {
                        dbModel.SaveChanges();
                    }
                    catch (Exception)
                    {
                        //return "插入Contact异常";
                        //return "系统出现未知错误，请稍候再试";
                        return 2;
                    }
                    //根据刚刚插入的Contact表的ID，再添加到Number表
                    Number n2 = new Number();
                    n2.Number1 = number;
                    n2.AddressID = con.ID;
                    dbModel.AddToNumber(n2);
                    try
                    {
                        dbModel.SaveChanges();
                    }
                    catch (Exception)
                    {
                        //return "插入Number异常"; 
                        //return "系统出现未知错误，请稍候再试";
                        return 2;
                    }
                    //return "成功的向Contact,Number各插入一条记录";
                    //return "联系人添加成功";
                    return 3;
                }
            }
            throw new NotImplementedException();
        }


        public List<AddressSendInfoModel> GetNumberByText(string text, int UserId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var s = from n in dbModel.Number
                        join c in dbModel.Contact
                        on n.AddressID equals c.ID
                        where c.DisplayName.Contains(text) || n.Number1.Contains(text)
                        where c.UserID == UserId
                        select new
                        {
                            number = n.Number1,
                            name = c.DisplayName
                        };
                List<AddressSendInfoModel> list = new List<AddressSendInfoModel>();
                foreach (var n in s)
                {
                    AddressSendInfoModel _list = new AddressSendInfoModel();
                    _list.Name = n.name;
                    _list.Number = n.number;
                    list.Add(_list);
                }
                return list;
            }
            throw new NotImplementedException();
        }

        public bool DeleteMessageById(int Id)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var info = dbModel.Message.SingleOrDefault(e => e.ID == Id);
                dbModel.Message.DeleteObject(info);
                try
                {
                    dbModel.SaveChanges();
                }
                catch
                {
                    return false;
                }
                return true;
            }
            throw new NotImplementedException();
        }

        public bool DeleteNumberByAddressId(int addressId, string addnumber)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var info = dbModel.Number.FirstOrDefault(n => n.AddressID == addressId && n.Number1 == addnumber);
                dbModel.Number.DeleteObject(info);
                try
                {
                    dbModel.SaveChanges();
                }
                catch
                {
                    return false;
                }
                return true;
            }
            throw new NotImplementedException();
        }
        public int GetPageCount(int UserId, int max, string key)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var s = from n in dbModel.Number
                        join c in dbModel.Contact
                        on n.AddressID equals c.ID
                        where c.DisplayName.Contains(key) || n.Number1.Contains(key)
                        where c.UserID == UserId
                        select new
                        {
                            number = n.Number1,
                            name = c.DisplayName
                        };
                int count = (s.Count() + (max - 1)) / max;
                return count;
            }
        }


        public List<AddressSendInfoModel> GetNumberByUsers(int UserId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var s = from n in dbModel.Number
                        join c in dbModel.Contact
                        on n.AddressID equals c.ID
                        where c.UserID == UserId
                        orderby c.ID
                        select new
                        {
                            number = n.Number1,
                            name = c.DisplayName,
                            addressId = n.AddressID,
                            ContactID = c.ID
                        };
                List<AddressSendInfoModel> list = new List<AddressSendInfoModel>();
                foreach (var n in s)
                {
                    AddressSendInfoModel _list = new AddressSendInfoModel();
                    _list.Name = n.name;
                    _list.Number = n.number;
                    _list.AddressId = n.addressId;
                    _list.IsInGroup = false;
                    _list.ID = n.ContactID;
                    list.Add(_list);
                }
                return list;
            }
            throw new NotImplementedException();
        }


        public bool UpdateAddress(string name, string number, int id)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var updateContactitem = dbModel.Contact.FirstOrDefault(a => a.ID == id);
                updateContactitem.DisplayName = name;


                var firstNumber = updateContactitem.Number.First();
                dbModel.DeleteObject(firstNumber);

                Number newNumber = new Number();
                newNumber.Number1 = number;

                updateContactitem.Number.Add(newNumber);


                dbModel.SaveChanges();

                //var updateNumberitem = dbModel.Number.FirstOrDefault(b => b.AddressID == id);


                //updateNumberitem.Number1 = number;
                //updateNumberitem.AddressID = updateNumberitem.AddressID;
                //dbModel.SaveChanges();
                return true;
            }
        }

        #region ContactGroup
        public int CreateNewGroup(int UserID, string name)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                var temp = dbModel.Group.FirstOrDefault(t => t.GroupName == name);
                if (temp != null)
                    return -1; // 该分组已经存在
                Group g = new Group();
                g.GroupName = name.Replace(";", "");
                g.UserID = UserID;
                dbModel.Group.AddObject(g);
                dbModel.SaveChanges();
                return g.ID;
            }
        }


        public List<AddressGroupInfo> GetGroupsByUserid(int userid)
        {
            using (var dbacc = new Entities(_connectionString))
            {
                var data = dbacc.Group.Where(g => g.UserID == userid).OrderBy(g => g.ID);
                List<AddressGroupInfo> list = new List<AddressGroupInfo>();
                if (data != null)
                    foreach (var item in data)
                    {
                        AddressGroupInfo agi = new AddressGroupInfo();
                        agi.ID = item.ID;
                        agi.Name = item.GroupName;
                        list.Add(agi);
                    }
                return list;
            }
        }
        
        public List<AddressGroupInfo> GetGroupsByUserid(int userid, int page, int max)
        {
            using (var dbacc = new Entities(_connectionString))
            {
                var data = dbacc.Group.Where(g => g.UserID == userid).OrderBy(g => g.ID).Skip((page - 1) * max).Take(max); 
                List<AddressGroupInfo> list = new List<AddressGroupInfo>();
                if (data != null)
                    foreach (var item in data)
                    {
                        AddressGroupInfo agi = new AddressGroupInfo();
                        agi.ID = item.ID;
                        agi.Name = item.GroupName;
                        list.Add(agi);
                    }
                return list;
            }
        }

        public int GetGroupPageCount(int userid, int max)
        {
            using (var dbacc = new Entities(_connectionString))
            {
                var data = dbacc.Group.Where(g => g.UserID == userid).OrderBy(g => g.ID);
                int count = (data.Count() + (max - 1)) / max;
                return count;
            }
        }

        public List<AddressSendInfoModel> GetContactsByGroupID(int userid, int groupid)
        {
            using (var dbacc = new Entities(_connectionString))
            {
                List<AddressSendInfoModel> list = GetNumberByUsers(userid);
                if (groupid != -1)
                {
                    var data = dbacc.Group.FirstOrDefault(g => g.ID == groupid);
                    if (data == null)
                    {
                        list.Clear();
                        return list;
                    }
                    else
                    {
                        foreach (var item in data.Contact)
                        {
                            var contact = list.FirstOrDefault(c => c.ID == item.ID);
                            if (contact != null)
                                contact.IsInGroup = true;
                        }
                    }
                }
                else
                {
                    List<AddressSendInfoModel> list3 = new List<AddressSendInfoModel>();
                    foreach (var item in list)
                    {
                        var contact = dbacc.Contact.FirstOrDefault(c => c.ID == item.ID);
                        if (contact != null)
                        {
                            if (contact.Group.Count == 0)
                                list3.Add(item);
                        }
                    }
                    return list3.OrderBy(o => o.Name).ToList();
                }

                var list2 = list.OrderBy(c => c.ID).OrderByDescending(cc => cc.IsInGroup);
                return list2.ToList();
            }
        }

        public int SaveRelations(int userid, int groupid, int[] items)
        {
            //if(items!=null && items.Count() > 0)
            using (var dbacc = new Entities(_connectionString))
            {
                var data = dbacc.Group.FirstOrDefault(g => g.ID == groupid);
                if (data != null)
                {
                    data.Contact.Clear();
                    if (items != null)
                        foreach (var item in items)
                        {
                            var c = dbacc.Contact.FirstOrDefault(cc => cc.ID == item);
                            if (c != null)
                                data.Contact.Add(c);
                        }
                    dbacc.SaveChanges();
                }
            }
            return 0;
        }

        public int RemoveGroup(int userid, int[] groupids)
        {
            using (var dbacc = new Entities(_connectionString))
            {
                foreach (var groupid in groupids)
                {
                    var g = dbacc.Group.FirstOrDefault(gg => gg.ID == groupid && gg.UserID == userid);
                    if (g != null)
                    {
                        g.Contact.Clear();
                        dbacc.Group.DeleteObject(g);
                    }
                }
                dbacc.SaveChanges();                
            }
            return 0;
        }

        public List<AddressSendInfoModel> GetNumberWithGroupByUsers(int UserId)
        {
            using (var dbModel = new Entities(_connectionString))
            {
                List<AddressSendInfoModel> list = new List<AddressSendInfoModel>();

                var data = dbModel.Group.Where(g => g.UserID == UserId).OrderBy(g => g.ID);

                if (data != null)
                    foreach (var item in data)
                    {
                        AddressSendInfoModel agi = new AddressSendInfoModel();
                        agi.ID = item.ID;
                        agi.Name = "[G]" + item.GroupName;
                        agi.Number = "";
                        list.Add(agi);
                    }


                var s = from n in dbModel.Number
                        join c in dbModel.Contact
                        on n.AddressID equals c.ID
                        where c.UserID == UserId
                        orderby c.ID
                        select new
                        {
                            number = n.Number1,
                            name = c.DisplayName,
                            addressId = n.AddressID,
                            ContactID = c.ID
                        };
                foreach (var n in s)
                {
                    AddressSendInfoModel _list = new AddressSendInfoModel();
                    _list.Name = n.name;
                    _list.Number = n.number;
                    _list.AddressId = n.addressId;
                    //_list.IsInGroup = false;
                    // _list.ID = n.ContactID;
                    list.Add(_list);
                }
                return list;
            }
        }

        public List<AddressExportInfo> GetContactsForExport(int userid)
        {
            using (var dbacc = new Entities(_connectionString))
            {
                List<AddressExportInfo> list = new List<AddressExportInfo>();
                var contacts = from n in dbacc.Number
                               join c in dbacc.Contact
                               on n.AddressID equals c.ID
                               where c.UserID == userid
                               orderby c.ID
                               select new
                               {
                                   number = n.Number1,
                                   name = c.DisplayName,
                                   addressId = n.AddressID,
                                   Groups = c.Group
                               };
                if (contacts != null)
                {
                    foreach (var contact in contacts)
                    {
                        AddressExportInfo aei = new AddressExportInfo();
                        aei.Name = contact.name;
                        aei.Number = contact.number;
                        aei.Group = "";
                        if (contact.Groups != null)
                            foreach (var g in contact.Groups)
                            {
                                aei.Group += g.GroupName + ";";
                            }
                        list.Add(aei);
                    }
                }
                return list;
            }
        }
        #endregion


        public int ImportContact(System.Data.DataTable dt, int userid)
        {
            using (var dbacc = new Entities(_connectionString))
            {
                var user = dbacc.User.FirstOrDefault(u => u.UserID == userid);

                foreach (DataRow item in dt.Rows)
                {
                    string name = item["name"].ToString();
                    var temp = user.Contact.FirstOrDefault(c => c.DisplayName == name);
                    Contact contact;
                    if (temp == null)
                    {
                        contact = new Contact();
                        user.Contact.Add(contact);
                        contact.DisplayName = name;
                    }
                    else
                    {
                        contact = temp;
                    }
                    contact.Enabled = true;

                    contact.Number.Clear();
                    Number number = new Number();
                    number.Number1 = item["number"].ToString();
                    contact.Number.Add(number);

                    contact.Group.Clear();
                    string[] groups = item["group"].ToString().Split(';');
                    foreach (var group in groups)
                    {
                        string groupname = group.Trim();
                        if (groupname == "")
                            continue;
                        var g = dbacc.Group.FirstOrDefault(gg => gg.UserID == userid && gg.GroupName == groupname);
                        if (g != null)
                        {
                            contact.Group.Add(g);
                        }
                        else
                        {
                            Group newgroup = new Group();
                            newgroup.GroupName = groupname;
                            newgroup.Contact.Add(contact);

                            user.Group.Add(newgroup);
                            dbacc.SaveChanges();
                        }
                    }


                }
                dbacc.SaveChanges();
            }
            return 0;
        }
    }
}
